﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsMaterialRequirementDataAccess
    {
        public static System.Data.DataTable SelectList(string MaterialRequirementID, string From, string End, int pageIndex, int pageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_MaterialRequirement o where 1=1 ";
                    if (!string.IsNullOrEmpty(MaterialRequirementID))
                    {
                        strcmd += " and o.MaterialRequirementID like @MaterialRequirementID";
                        dpc.AddWithValue("@MaterialRequirementID", "%" + MaterialRequirementID + "%");
                    }
                    if (!string.IsNullOrEmpty(From))
                    {
                        strcmd += " and o.BillDate >= @From";
                        dpc.AddWithValue("@From", From + " 00:00:00");
                    }
                    if (!string.IsNullOrEmpty(End))
                    {
                        strcmd += " and o.BillDate <= @End";
                        dpc.AddWithValue("@End", End + " 23:59:59");
                    }
                    strcmd += " order by o.BillDate desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogList(string PlanID, string ProductName, string userid, int pageIndex, int pageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select v.* from V_QuerySPlanByMatRequirement v where 1=1 ";
                    if (!string.IsNullOrEmpty(PlanID))
                    {
                        strcmd += " and v.PlanID like @PlanID";
                        dpc.AddWithValue("@PlanID", "%" + PlanID + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and v.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + PlanID + "%");
                    }
                    strcmd += " order by v.BillDate desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogMaterial(string PlanID, string MaterialRequirementID, string MaterialID, string MaterialName, int pageIndex, int PageSize, out int m_count)
        {

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select pl.*,ISNULL(pl.TotalNum,0)-ISNULL(c.AdviseNumTotal,0) as LastNum,m.MaterialName,
                                    Convert(varchar(18),m.Long,23)+'*'+Convert(varchar(18),m.Width,23)+'*'+Convert(varchar(18),m.Height,23)+'.'+Convert(varchar(18),m.InSide,23)+'.'+Convert(varchar(18),m.OutSide,23) as Specifications,
                                    m.IsDirect,m.Unit,v.StockNum,pu.PuLastNum as OnWayNum,m.MinOrderWeight,bd.MMNumber,Convert(decimal(18,2),ISNULL(bd.SingleWeight,1)*1000/ISNULL(m.MeterWeight,1)) as MeterWeight,
                                    ISNULL(pl.TotalNum,0)-ISNULL(c.AdviseNumTotal,0)-ISNULL(pu.PuLastNum,0)-ISNULL(v.StockNum,0) as AdviseNum
                                    from BS_PlanDetail pl 
                                    left join (select PlanDetailID,MaterialID,SUM(ISNULL(AdviseNum,0)) as AdviseNumTotal
                                    from BS_MaterialRequirementDetail where MaterialRequirementID <> @MaterialRequirementID group by PlanDetailID,MaterialID) c on c.PlanDetailID=pl.PlanDetailID and c.MaterialID=pl.MaterialID --需求累计数量
                                    left join V_OnWayNum pu on pu.MaterialID=pl.MaterialID   ---在途数量
                                    left join V_StockNum v on v.ID=pl.MaterialID   --库存数量
                                    left join MD_Material m on m.MaterialID=pl.MaterialID  --原料表
                                    left join BS_BOMDetail bd on bd.BOMDetailID=pl.BOMDetailID
                                    where ISNULL(pl.TotalNum,0)-ISNULL(c.AdviseNumTotal,0)-ISNULL(pu.PuLastNum,0)-ISNULL(v.StockNum,0)>0 ";

                    dpc.AddWithValue("@MaterialRequirementID", MaterialRequirementID);
                    if (!string.IsNullOrEmpty(PlanID))
                    {
                        strcmd += " and pl.PlanID = @PlanID";
                        dpc.AddWithValue("@PlanID", PlanID);
                    }
                    if (!string.IsNullOrEmpty(MaterialID))
                    {
                        strcmd += " and pl.MaterialID like @MaterialID";
                        dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                    }
                    if (!string.IsNullOrEmpty(MaterialName))
                    {
                        strcmd += " and m.MaterialName like @MaterialName";
                        dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
                    }
                    strcmd += " order by pl.MaterialID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetDataTablebByID(string id)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_MaterialRequirement o where MaterialRequirementID=@MaterialRequirementID ";

                    dpc.AddWithValue("@MaterialRequirementID", id);
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
